<template>
  <div class="WellhistoryQuery">
    <el-form>
      <el-autocomplete
        v-model="wellname"
        :fetch-suggestions="getallwellnames"
        class="inline-input"
        placeholder="输入井号"
      />
      <el-button :loading="querying" type="primary" @click="queryclick">
        查询
      </el-button>
      <el-alert
        :title="sql_alert_message"
        type="error"
        center
        :style="sql_alert_display"
        show-icon
        :closable="true"
      ></el-alert>
    </el-form>
    <div class="ag-toolbar">
      <button
        id="undoBtn"
        class="ag-btn"
        v-on:click="undo()"
        :disabled="agbtndis.undo"
      >
        <el-icon><DArrowLeft /></el-icon>撤销
      </button>
      <button
        id="redoBtn"
        class="ag-btn"
        v-on:click="redo()"
        :disabled="agbtndis.redo"
      >
        <el-icon><DArrowRight /></el-icon>恢复
      </button>
      <button
        id="addrowBtn"
        class="ag-btn"
        v-on:click="addrow()"
        :disabled="agbtndis.addrow"
      >
        <el-icon><Plus /></el-icon>添加行
      </button>
      <button
        id="delrowBtn"
        class="ag-btn"
        v-on:click="delrow()"
        :disabled="agbtndis.delrow"
      >
        <el-icon><Minus /></el-icon>删除行
      </button>
      <button
        id="saveBtn"
        class="ag-btn"
        v-on:click="savetab()"
        :disabled="agbtndis.save"
      >
        <el-icon><UploadFilled /></el-icon>提交至数据库
      </button>

      <button
        id="impoBtn"
        class="ag-btn"
        v-on:click="openImpo()"
        :disabled="agbtndis.import"
      >
        <el-icon><Upload /></el-icon>导入数据
      </button>
      <input
        type="file"
        ref="importElem"
        multiple
        accept="text/plain,text/csv"
        style="display: none"
        :oninput="impotab"
      />
      <button
        id="expoBtn"
        class="ag-btn"
        v-on:click="expotab()"
        :disabled="agbtndis.export"
      >
        <el-icon><Download /></el-icon>下载
      </button>
    </div>
    <ag-grid-vue
      class="ag-theme-balham"
      :rowData="table_data"
      :columnDefs="query_columns"
      :defaultColDef="defaultColDef"
      :undoRedoCellEditing="undoRedoCellEditing"
      :enableCellChangeFlash="enableCellChangeFlash"
      :rowSelection="rowSelection"
      :editType="editType"
      :onCellValueChanged="onCellValueChanged"
      :onGridReady="onGridReady"
      style="width: 100%; height: 700px"
    ></ag-grid-vue>
  </div>
</template>

<script setup>
import { dbquery } from "@/apis/query";
import { ref } from "vue";
import { ElMessage } from "element-plus";
import allwellnames from "@/apis/getallwellnames";
import columnsnorm from "@/apis/columnsnorm";
import "ag-grid-enterprise";
import { AgGridVue } from "ag-grid-vue3";

import AgDatePicker from "@/components/AgDatePicker.vue";
import AgNumCell from "@/components/AgNumCell.vue";
import { DataFrame, readCSV, toJSON } from "danfojs";
//import LineChart from "@/components/LineChart";
/*
import { ModuleRegistry } from "@ag-grid-community/core";
import { ClipboardModule } from "@ag-grid-enterprise/clipboard";
import { RangeSelectionModule } from "@ag-grid-enterprise/range-selection";

ModuleRegistry.registerModules([RangeSelectionModule, ClipboardModule]);
*/

var colnms = ref();

colnms.value = getcolnm();
const onGridReady = (params) => {
  gridApi = params.api;
  gridColumnApi = params.columnApi;
  colnms.value = getcolnm();
};

var wellname = ref("");
var editType = ref();
var defaultColDef = {
  sortable: true,
  resizable: true,
  editable: true,
  wrapText: true,
  autoHeight: true,
  filter: true,
};
var gridApi = null;
var gridColumnApi = null;
var agbtndis = ref({
  undo: true,
  redo: true,
  addrow: true,
  delrow: true,
  save: true,
  import: false,
  export: false,
});
function autoSizeAll() {
  gridColumnApi.autoSizeAllColumns(true);
}
function updateAgbtndis() {
  agbtndis.value.undo = gridApi.getCurrentUndoSize() > 0 ? false : true;
  agbtndis.value.redo = gridApi.getCurrentRedoSize() > 0 ? false : true;
  agbtndis.value.save = gridApi.getCurrentUndoSize() > 0 ? false : true;
  agbtndis.value.addrow = wellname.value != "" ? false : true;
  agbtndis.value.delrow =
    gridApi.getDisplayedRowCount() > 0 && wellname.value != "" ? false : true;
}
/*
function updateAgbtnRow() {
  //updata ag-grid-buttons-row states
  if (wellname.value == "") {
    agbtndis.value.addrow = false;
    agbtndis.value.save = false;
  }
  if (gridApi.getDisplayedRowCount() > 0) {
    agbtndis.value.delrow = false;
  }
}
*/
function onCellValueChanged() {
  updateAgbtndis();
}
function getcolnm() {
  //get table columns' name
  var res = [];
  const sql =
    "SELECT COLUMN_NAME  FROM information_schema.columns WHERE table_name='test1'";
  dbquery("dbpytrol", sql).then(async (result) => {
    result.data.forEach((element) => res.push(...Object.values(element)));
  });
  return res;
}
function undo() {
  gridApi.undoCellEditing();
}
function redo() {
  gridApi.redoCellEditing();
}
function getFormatDate(date) {
  var y = date.getFullYear();
  var m = date.getMonth() + 1;
  var d = date.getDate();
  return y + (m < 10 ? "-0" : "-") + m + (d < 10 ? "-0" : "-") + d;
}
function addrow(
  items = [{ 井号: wellname.value, 日期: getFormatDate(new Date()) }]
) {
  //var today = new Date();
  //var now = getFormatDate(today);
  var newItem = items;
  gridApi.applyTransaction({
    add: newItem,
  });
  updateAgbtndis();
  autoSizeAll();
  agbtndis.value.save = false;
}
function delrow() {
  var selectedRows = gridApi.getSelectedRows();
  gridApi.applyTransaction({ remove: selectedRows });
  updateAgbtndis();
  agbtndis.value.save = false;
}
function getGridData() {
  const rowData = [];
  gridApi.forEachNode(function (node) {
    rowData.push(node.data);
  });
  return rowData;
}
var importElem = ref();
function openImpo() {
  importElem.value.value = "";
  importElem.value.click();
  //var importData = savetab({ tabData: importData });
}
function impotab() {
  if (importElem.value != "") {
    var data = new DataFrame([new Array(colnms.value.length)], {
      columns: colnms.value,
    });
    for (const i in importElem.value.files) {
      readCSV(importElem.value.files[i], { encoding: "GB2312" }).then((df) =>
        addrow(toJSON(df))
      );
    }
  }
}
function expotab() {
  gridApi.exportDataAsCsv({ fileName: wellname.value + "井史" });
}
function savetab(
  rawData = JSON.parse(JSON.stringify(query_result)),
  tabData = getGridData()
) {
  var sql = "";
  var resData = [];
  var tabindex = [];
  tabData.forEach((tabEle, index) => {
    if (tabEle["序号"]) tabindex.push(tabEle["序号"]);

    //新表记录不包含在原表中, replace INTO `pytrol`.`test1`(`井号`, `日期`, `记录类型`, `详情`, `层位标记`, `备注`, `前日产液`, `前日产油`, `前含水`, `前动液面`, `后日产液`, `后日产油`, `后含水`, `后动液面`) VALUES ('2', '2022-03-15', '33', '33', '33', '33', 55, 66, 22, 33, 44, 22, 11, 22)
    if (
      !rawData.some((rawEle) => {
        return JSON.stringify(rawEle) == JSON.stringify(tabEle);
      })
    ) {
      var sql1 = "";
      var sql2 = "";
      for (let key in tabEle) {
        sql1 = sql1 + key + ",";
        sql2 = sql2 + "'" + tabEle[key] + "'" + ",";
      }
      sql1 = sql1.substring(0, sql1.length - 1);
      sql2 = sql2.substring(0, sql2.length - 1);
      sql = `replace INTO pytrol.test1(${sql1})  VALUES (${sql2}) `;
      dbquery("dbpytrol", sql)
        .catch(async (reason) => {
          ElMessage({
            duration: 0,
            showClose: true,
            type: "error",
            message: reason,
          });
        })
        .then(async (result) => {
          if (result.data.err) {
            ElMessage({
              duration: 0,
              showClose: true,
              type: "error",
              message: (
                result.data.err +
                "   :   " +
                result.data.message
              ).replace("row 1", "row " + String(index + 1)),
            });
          } else {
            ElMessage({
              type: "success",
              message: "保存成功",
            });
            queryclick();
          }
        });
    }
  });
  rawData.forEach((rawEle) => {
    //记录不存在与新表中，删除
    if (!tabindex.includes(Object.values(rawEle)[0])) {
      sql =
        "DELETE FROM `pytrol`.`test1` WHERE 井号= '" +
        wellname.value +
        "'" +
        " and 序号 = " +
        Object.values(rawEle)[0];
      dbquery("dbpytrol", sql)
        .catch(async (reason) => {
          console.log(reason);
        })
        .then(async (result) => {
          if (result.data.err) {
            ElMessage({
              duration: 0,
              showClose: true,
              type: "error",
              message: result.data.err + "   :   " + result.data.message,
            });
          } else {
            ElMessage({
              type: "success",
              message: "保存成功",
            });
            queryclick();
          }
        });
    }
  });
}

var querying = false;
var rowSelection = ref("multiple");
var undoRedoCellEditing = ref(true);
var enableCellChangeFlash = ref(true);
var sql_alert_message = ref("");
var sql_alert_display = ref("visibility:hidden");
var query_result = null;
var table_data = ref([]);
var query_columns = ref([]);

const coldefs = {
  井号: {
    editable: false,
    pinned: "left",
  },
  序号: { hide: true },
  日期: {
    pinned: "left",
    cellEditorSelector: () => {
      return {
        component: AgDatePicker,
      };
    },
  },
  前日产油: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  后日产油: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  前日产液: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  后日产液: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  前动液面: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  后动液面: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  前含水: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  后含水: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  前累产油万吨: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  前累产水万吨: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  前累注水万方: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  措施有效天数: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
  措施累增油: {
    cellEditorSelector: () => {
      return {
        component: AgNumCell,
      };
    },
  },
};

//query_columns.value = columnsnorm(colnms.value, ["field"], coldefs);

query_columns.value = columnsnorm(
  [
    "序号",
    "井号",
    "日期",
    "记录类型",
    "详情",
    "层位标记",
    "前日产液",
    "前日产油",
    "前含水",
    "前动液面",
    "后日产液",
    "后日产油",
    "后含水",
    "后动液面",
    "前累产油万吨",
    "前累产水万方",
    "前累注水万方",
    "措施有效天数",
    "措施累增油吨",
    "井况",
    "备注",
  ],
  ["field"],
  coldefs
);

function queryclick() {
  sql_alert_display.value = "visibility:hidden";
  sql_alert_message.value = "";
  querying = true;
  wellname.value = wellname.value.replace(/\s*/g, "");
  var sql =
    "select 序号,井号,DATE_FORMAT(日期,'%Y-%m-%d') as 日期  ,记录类型 ,详情 ,层位标记,封层,补层 ,前日产液 ,前日产油 ,前含水 ,前动液面 ,后日产液 ,后日产油 ,后含水 ,后动液面,产量对比,前累产油万吨,前累产水万方,前累注水万方,措施有效天数,措施累增油吨 ,井况,备注 from pytrol.test1 ";

  if (wellname.value == "") {
    ElMessage({
      message: "请输入井号.",
      type: "warning",
    });
  } else {
    sql = sql + " where 井号='" + wellname.value + "' order by 日期 asc";
  }
  dbquery("dbpytrol", sql)
    .catch(async (reason) => {
      sql_alert_message.value = reason.message;
      sql_alert_display.value = "visibility:visible";
    })
    .then(async (result) => {
      if (result.data.err) {
        sql_alert_message.value = result.data.err + ":" + result.data.message;
        sql_alert_display.value = "visibility:visible";
      } else if (result.data.length == 0) {
        ElMessage({
          message: "井史数据为空",
          type: "warning",
        });
        //sql_alert_message.value = "井史数据为空";
        //sql_alert_display.value = "visibility:visible";
        query_result = JSON.parse(JSON.stringify(result.data));
        table_data.value = result.data;
      } else {
        //query_result1 = queryresnorm(result.data, "dbass", "echarts");
        query_result = JSON.parse(JSON.stringify(result.data));
        query_columns.value = columnsnorm(
          Object.keys(query_result[0]),
          ["field"],
          coldefs
        );
        table_data.value = result.data;
        console.log(query_columns.value);
        console.log(table_data.value);
      }
    })
    .then(async (res) => {
      updateAgbtndis();
      autoSizeAll();
    });
}

const getallwellnames = (queryString, callback) => {
  const results = queryString
    ? allwellnames.filter(createFilter(queryString))
    : allwellnames;
  callback(results);
};
const createFilter = (queryString) => {
  return (restaurant) => {
    return (
      restaurant.value.toLowerCase().indexOf(queryString.toLowerCase()) === 0
    );
  };
};
</script>

<style scoped>
.WellhistoryQuery {
  width: 100%;
  height: 100%;
}
.el-alert {
  display: inline;
  visibility: hidden;
  padding-right: 5px;
  padding-bottom: 5px;
}

button,
.el-form {
  margin-bottom: 5px;
}
</style>
